Combo box based on another combo box

[StuckAndNoClue]

I've got two combo boxes on a form:
1. Division Name - "_" (Blank), "Division1", "Division2" etc
2. Members of a selected Division

What I want to do is select a division and then i want the 2nd combo box to show only the members of the selected Division. However, if i select the blank division "_", i want it to show me all members of all divisions.

At the moment, if i select a Division, it comes up with only the members in the selected Division. I don't know how to get it so that if i choose the blank division, all names come up.

i tried putting in the criteria for the second combo box as follows:
IIf([Forms]![FormName]![DivisionName]=" ", <>[Forms]![FormName]![DivisionName], [Forms]![FormName]![DivisionName])

But the result is the same as if i just limited the criteria just to be [Forms]![FormName]![DivisionName], - the result only achieves the first part of what i want getting done.

Sorry if i sounded too confusing for you to follow.

[Tanis]

You need to look at cascading combo boxes. Try this site. Look under cascading combo box.

http://www.utterangel.com/pages/access_downloads.aspx

[StuckAndNoClue]

You need to look at cascading combo boxes. Try this site. Look under cascading combo box.

http://www.utterangel.com/pages/access_downloads.aspx

Thanks Tanis but i find that the cascading combo boxes was too complicated for me to follow, any more ideas?

[StuckAndNoClue]

Thanks, i'm just going to look up cascading combo boxes to work this one out. i thought it would be a simple solution, without much usage of vba, but it doesnt appear to be so.

Thanks anyway.

[tara99]

Thanks, i'm just going to look up cascading combo boxes to work this one out. i thought it would be a simple solution, without much usage of vba, but it doesnt appear to be so.

Thanks anyway.

When you find the solution, let me know, becasue I have the same problem
Thanks

[MMcCarthy]

OK guys this isn't that complicated but you will need some VBA code.

In the After Update Event of Combo1 put ...

Private Sub Division_Name_AfterUpdate()
 
   SELECT CASE Division_Name
 
   CASE " " ' I'm assuming this is the value in the blank row
      Me.Members.RowSource = "SELECT Member FROM TableName;"
 
   CASE ELSE
      Me.Members.RowSource = "SELECT Member FROM TableName WHERE DivisionName = [Froms]![FormName]![DivisionName];"
 
   END SELECT
 
   Me.Members.Requery
 
End Sub
 

Mary

[jamesnkk]

Let assume your 1st combo box called cboDivision, in the After event, you add this statement, I just show an example, you need to fill up the other column.

I am just coping my coding over here.

Dim sManagerSource As String

sManagerSource = "SELECT [tablename].[DivisonID] as divsionID,......." & _
"WHERE tablename.divsionID = " & Me.cboDivision.Column(0)


2ndCombobox.RowSource = sManagerSource

Try this, just 2 line of code,I have this problem before, now for me it solved.
Also try to put those statement in the Form Current, so the next time when you edit the form, just click the 2nd combo box it will remember the selection from the first combo box, this way you don't have to select 1st combo box and then select 2nd combo box, unless you want to make another selection on your 1st combo box.

Happy coding